import sqlite3
from sspai.items import GaoqingItem

class Sqlite3Pipeline(object):

    def __init__(self, sqlite_file, sqlite_table):
        self.sqlite_file = sqlite_file
        self.sqlite_table = sqlite_table
        
    @classmethod
    def from_crawler(cls, crawler):
        return cls(
            sqlite_file = crawler.settings.get('SQLITE_FILE'), # 从 settings.py 提取
            sqlite_table = crawler.settings.get('SQLITE_TABLE', 'items')
        )

    def open_spider(self, spider):
        self.conn = sqlite3.connect(self.sqlite_file)
        # self.cur = self.conn.cursor()
        self._create_table(self.sqlite_table,GaoqingItem.fields.keys(),None)

    def close_spider(self, spider):
        self.conn.close()

    def process_item(self, item, spider):
        insert_sql = "insert into {0}({1}) values ({2})".format(self.sqlite_table, 
                                                                ', '.join(item.fields.keys()),
                                                                ', '.join(['?'] * len(item.fields.keys())))
        print(insert_sql)
        value_list =[]
        for f in item.keys():
            value_list.append(item[f])
        print(value_list)
        self.conn.execute(insert_sql, value_list)
        self.conn.commit()
        
        return item

    def _create_table(self, table_name, columns, keys = None):
        sql = 'create table if not exists [%s] ' % table_name
        
        column_define = ['[%s] text' % column for column in columns]
        column_define.append('[Id] INTEGER PRIMARY KEY')
        print('type: %s' % type(keys))
        if keys:
            if len(keys) > 0:
                primary_key = 'primary key (%s)' % ', '.join(keys[0])
                column_define.append(primary_key)
                
            for key in keys[1:]:
                column_define.append('unique (%s)' % ', '.join(key))
        
        sql += '(%s)' % ', '.join(column_define)
        
        print('sql: %s' % sql)
        self.conn.execute(sql)
        self.conn.commit()
